Imports WRDB.Controls

Public Class frmSupport
    Inherits System.Windows.Forms.Form

    Private WithEvents bwGetTable As System.ComponentModel.BackgroundWorker
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents cboDisplay As System.Windows.Forms.ComboBox
    Friend WithEvents chkAllProjects As System.Windows.Forms.CheckBox
    Private DataAdapter(clsProject.enumTableTypes.Tracking) As DbDataAdapter
    Private ValidateDataAdapter As DbDataAdapter
    Friend WithEvents cboTableGroup As System.Windows.Forms.ComboBox
    Private bsTracking As New BindingSource
    Private FilterString As String = ""
    Private WithEvents dgeBranches, dgeConvGrp, dgePCodeGrp, dgePCodes, dgeStaGrp, dgeStations, dgeTracking, dgeValidation, dgeCriteria As WRDB.Controls.DGVEditor

    Private Sub frmSupport_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        If MainForm IsNot Nothing Then MainForm.ProgressComplete() : MainForm.Activate()
        With chkStationDropdown
            SaveSetting(REGAPP, Me.Name, .Name, .Checked)
        End With
        With chkPCodeDropDown
            SaveSetting(REGAPP, Me.Name, .Name, .Checked)
        End With
        With chkCriteriaDropDown
            SaveSetting(REGAPP, Me.Name, .Name, .Checked)
        End With
        If bwGetTable IsNot Nothing Then
            With bwGetTable
                .CancelAsync()
                Do
                    Application.DoEvents()
                Loop Until Not .IsBusy
                .Dispose()
            End With
        End If
        If WRDBForms.Contains(Me.Text) Then WRDBForms.Remove(Me.Text)
        SaveWindowPos(REGAPP, Me)
    End Sub

    Private Sub frmSupport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        EnableControls(False)

        With chkStationDropdown
            .Checked = GetSetting(REGAPP, Me.Name, .Name, "True") And Project.IsLoggedIn
        End With
        With chkPCodeDropDown
            .Checked = GetSetting(REGAPP, Me.Name, .Name, "True") And Project.IsLoggedIn
            .Enabled = Project.WQXVisible 'WQX are the only columns that could have drop-downs, so hide if not dealing with WQX
        End With
        With chkCriteriaDropDown
            .Checked = GetSetting(REGAPP, Me.Name, .Name, "True") And Project.IsLoggedIn
        End With

        dgeBranches = New WRDB.Controls.DGVEditor(dgBranches, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgeConvGrp = New WRDB.Controls.DGVEditor(dgConvGrp, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgePCodeGrp = New WRDB.Controls.DGVEditor(dgPCodeGrp, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgePCodes = New WRDB.Controls.DGVEditor(dgPCodes, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgeStaGrp = New WRDB.Controls.DGVEditor(dgStaGrp, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgeStations = New WRDB.Controls.DGVEditor(dgStations, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgeTracking = New WRDB.Controls.DGVEditor(dgTracking, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgeValidation = New WRDB.Controls.DGVEditor(dgValidate, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)
        dgeCriteria = New WRDB.Controls.DGVEditor(dgCriteria, MainWRDB.GridFont, MainWRDB.ReportScaling, PrintDoc)

        If IsMWForm Then btnGIS.Visible = False

        If Not Project.ResetPDox(clsProject.enumTableTypes.Stations) OrElse Not Project.ResetPDox(clsProject.enumTableTypes.Tracking) Then Exit Sub

        LoadTables()

        Me.cboDisplay.SelectedIndex = 0

        With cboValidate
            .Items.Clear()
            .Items.AddRange(New String() {"CCodes - Compositing codes associated with data records",
                                          "RCodes - Result codes associated with data records",
                                          "SCodes - Statistical codes associated with data records",
                                          "LCodes - Location codes associated with data records",
                                          "QCodes - Quality codes associated with Working tables, imported files, and data records",
                                          "Agencies - Valid agencies associated with Station IDs and data records",
                                          "FIPS Codes - Standard numeric codes associated with states and counties for Stations",
                                          "Station Types - Types of stations",
                                          "Basin Names - Names of major basins in which stations reside",
                                          "Water Body Types - Types of water bodies at which stations are located",
                                          "Eco Regions - Ecological regions in which stations reside",
                                          "Plan Regions - Planning regions in which stations reside"})
            If Project.WQXVisible Then
                .Items.AddRange(New String() {"WQX Characteristic Names - EPA Water Quality Exchange parameter names",
                                              "WQX Result Sample Fractions - EPA Water Quality Exchange sample fractions",
                                              "WQX Result Units - EPA Water Quality Exchange method identifiers",
                                              "WQX Methods - EPA Water Quality Exchange method contexts"})
            End If
            .SelectedIndex = 0
        End With

        WRDBForms.Add(Me, Me.Text)

    End Sub

    Private Sub EnableControls(ByVal Enabled As Boolean)
        TabControl1.Enabled = Enabled
        btnFind.Enabled = Enabled
        btnFilter.Enabled = Enabled
        btnCheck.Enabled = Enabled And Project.IsLoggedIn
        btnImport.Enabled = Enabled And Project.IsLoggedIn
        btnBuild.Enabled = Enabled And Project.IsLoggedIn
        btnApply.Enabled = Enabled And Project.IsLoggedIn
        btnGIS.Enabled = Enabled
        chkStationDropdown.Enabled = Enabled And Project.IsLoggedIn
        chkPCodeDropDown.Enabled = Enabled And Project.IsLoggedIn
        btnGroups.Enabled = Enabled And Project.IsLoggedIn
        btnSync.Enabled = Enabled And Project.IsLoggedIn

        Application.DoEvents()
    End Sub

    ''' <summary>
    ''' Load all (or selected) table into grid in the background
    ''' </summary>
    ''' <param name="SingleTableToLoad">If -1, will load all tables</param>
    ''' <remarks></remarks>
    Friend Sub LoadTables(Optional ByVal SingleTableToLoad As clsProject.enumTableTypes = -1)
        UseWaitCursor = True
        Application.DoEvents()
        LoadGroups()
        bwGetTable = New System.ComponentModel.BackgroundWorker
        With bwGetTable
            .WorkerReportsProgress = True
            .WorkerSupportsCancellation = True
            .RunWorkerAsync(SingleTableToLoad)
        End With
    End Sub

    Private Sub LoadGroups()
        For i As Integer = 1 To 3
            With CType(Choose(i, cboStationGrp, cboPCodeGrp, cboConvGrp), ComboBox)
                .Items.Clear()
                .Items.Add("(All Groups)")
                Dim sql As String = String.Format("SELECT DISTINCT {0} FROM {1} ORDER BY {0}", _
                    Choose(i, "StationGroup", "PCodeGroup", "ConvGroup"), _
                    Choose(i, "StaGrp", "PCodeGrp", "ConvGrp"))
                Dim dr As DbDataReader = Project.DB.ExecuteReader(sql)
                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then .Items.Add(dr.GetString(0))
                End While
                dr.Close()
                .SelectedIndex = 0
            End With
        Next
    End Sub

    Private Sub btnApply_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnApply.Click
        Try
            UpdateTables()
            If TabControl1.SelectedIndex = 6 Then FillTrackingCombos()
            ResetGrids(5)
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Try
            If bwGetTable IsNot Nothing Then
                Close()
                Exit Sub
            End If
            Dim NumChanges As Integer = 0
            For i As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Tracking
                If Project.DataSet.Tables(Project.TableName(i)).GetChanges IsNot Nothing Then NumChanges += Project.DataSet.Tables(Project.TableName(i)).GetChanges.Rows.Count
            Next

            If NumChanges > 0 AndAlso WarningMsgFmt("You have made changes to {0} rows in one or more of these Support tables. Do you want to write these changes to the database?", NumChanges) = Windows.Forms.DialogResult.OK Then
                If Not UpdateTables() Then Exit Sub
            End If
            'always force update of validate table
            UpdateValidateTable(True)

            Me.Close()
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    '<System.Diagnostics.DebuggerStepThrough()> _
    Friend Function UpdateTables() As Boolean
        Try
            MainForm.WriteProgress("Updating tables")

            If Not UpdateValidateTable(True) Then Return False

            If Not CheckCriteriaTable() Then Return False

            For i As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Tracking
                Dim TableName As String = Project.TableName(i)
                Dim dt As DataTable = Project.DataSet.Tables(TableName)

                If dt.GetChanges Is Nothing Then Continue For

                If Not Project.ResetPDox(TableName) Then Return False
                If Not MainForm.WriteProgress(i - clsProject.enumTableTypes.Stations + 1, clsProject.enumTableTypes.Tracking - clsProject.enumTableTypes.Stations + 1, String.Format("Updating {0} table in database...", TableName), ProgressBarStyle.Blocks) Then Exit For
                Try
                    Dim numkeys As Integer = Project.dsWRDB.Tables(i).PrimaryKey.Length
                    'make sure that primary keys have non-null values
                    For Each row As DataRow In dt.Rows
                        For c As Integer = 0 To numkeys - 1
                            If Not row.RowState = DataRowState.Deleted AndAlso IsDBNull(row.Item(c)) Then row.Item(c) = ""
                        Next
                    Next

                    'if table has CREATE_UPDATE field and record has changed, then set to current date/time (except Tracking, which refers to update of working table
                    '(also set if field is NULL, e.g., first time update)

                    Dim CreateUpdateCol As Integer = -1
                    For c As Integer = 0 To dt.Columns.Count - 1
                        If dt.Columns(c).ColumnName.ToUpper = "CREATE_UPDATE" Then CreateUpdateCol = c : Exit For
                    Next
                    If CreateUpdateCol <> -1 And TableName <> "Tracking" Then
                        For Each row As DataRow In dt.Rows
                            If row.RowState = DataRowState.Added Or row.RowState = DataRowState.Modified Or (row.RowState <> DataRowState.Deleted AndAlso IsDBNull(row(CreateUpdateCol))) Then
                                row(CreateUpdateCol) = Now
                            End If
                        Next
                    End If

                    If Project.DB.DatabaseType = clsDB.enumDBType.Paradox Then 'Or i = clsProject.enumTableTypes.Tracking Then
                        'FillTable will create the destination table and then write the contents of the DataTable there
                        'Unfortunately, when the DataTable was created, no information about primary keys was stored there
                        'Therefore, use the standard templates I have created to set the primary key property
                        dt.AcceptChanges()
                        Dim ar(numkeys - 1) As DataColumn
                        For j As Integer = 0 To numkeys - 1
                            ar(j) = dt.Columns(j)
                        Next
                        dt.PrimaryKey = ar
                        If Not Project.DB.SaveTable(TableName, dt) Then Return False
                    Else
                        Dim da As DbDataAdapter = DataAdapter(i)
                        Dim numrows As Integer = da.Update(dt)
                        dt.AcceptChanges()
                    End If
                Catch ex As Exception
                    WarningMsg(String.Format("An error occurred when trying to update the Support table: {0}. Make sure that you don't have any duplicate IDs. The error message was:\n\n{1}\n\nPlease correct the problem and try again.", TableName, ex.Message))
                    Return False
                End Try
            Next
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            MainForm.ProgressComplete()
        End Try
    End Function

    '<System.Diagnostics.DebuggerStepThrough()> _
    Private Sub frmSupport_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Activated
        If Me.MdiParent IsNot Nothing Then Me.ShowInTaskbar = False
    End Sub

    Private Sub bwGetTable_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwGetTable.DoWork
        Try
            Dim bw As System.ComponentModel.BackgroundWorker = sender
            With Project
                .DB.Cancelled = False
                If .DataSet Is Nothing Then .DataSet = New DataSet
                Dim onetabletype As clsProject.enumTableTypes = e.Argument
                Dim MinTT, MaxTT As clsProject.enumTableTypes
                If onetabletype = -1 Then
                    MinTT = clsProject.enumTableTypes.Stations
                    MaxTT = clsProject.enumTableTypes.Tracking
                Else
                    MinTT = onetabletype
                    MaxTT = MinTT
                End If
                For tabletype As clsProject.enumTableTypes = MinTT To MaxTT
                    If Not .ResetPDox(tabletype) Then e.Cancel = True : Exit Sub
                    If bw.CancellationPending Then e.Cancel = True : Exit Sub
                    bw.ReportProgress(tabletype)
                    Application.DoEvents()
                    'no need to make sure column exists in table as CheckSupportTableStructures is already called
                    'Dim colList As Generic.List(Of String) = Project.DB.GetColumnList(Project.TableName(tabletype))
                    'get key fields
                    Dim OrderBy As String = ""
                    For Each col As DataColumn In .GetTableTemplate(tabletype).Columns
                        'If colList.Contains(Project.DB.GetValidObjectName(col.ColumnName.ToUpper)) AndAlso .DB.IsPrimaryKey(.GetTableTemplate(tabletype), col) Then OrderBy &= IIf(OrderBy = "", " ORDER BY ", ",") & Project.DB.GetValidObjectName(col.ColumnName)
                        If .DB.IsPrimaryKey(.GetTableTemplate(tabletype), col) Then OrderBy &= IIf(OrderBy = "", " ORDER BY ", ",") & Project.DB.GetValidObjectName(col.ColumnName)
                    Next
                    DataAdapter(tabletype) = .DB.AddTable(Project.DataSet, .TableName(tabletype), "Select * from {0}{1}", .FullTableName(tabletype), OrderBy)
                    Try
                        .SetTableHeadings(tabletype)
                    Catch ex As Exception
                        WarningMsg(ex.Message)
                    End Try
                    Application.DoEvents()
                Next
            End With
        Catch ex As Exception
            WarningMsg("Unable to connect to database and access Support table.", , ex)
            e.Cancel = True
        End Try
    End Sub

    Private Sub bwGetTable_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bwGetTable.ProgressChanged
        With Project
            Dim numTables As Int16 = clsProject.enumTableTypes.Tracking - clsProject.enumTableTypes.Stations + 1
            Dim tableNum As Int16 = e.ProgressPercentage - clsProject.enumTableTypes.Stations + 1   'is tabletype, not percentage
            MainForm.WriteProgressOverall(tableNum, numTables, "Loading " & .TableName(e.ProgressPercentage) & " table...")
        End With
    End Sub

    Private Sub bwGetTable_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwGetTable.RunWorkerCompleted
        Try
            If Not e.Cancelled Then
                'now that data are retrieved, bind to grids and format them

                Dim numTables As Int16 = clsProject.enumTableTypes.Tracking - clsProject.enumTableTypes.Stations + 1

                For i As Integer = 1 To 8
                    Dim tblType As clsProject.enumTableTypes = Choose(i, clsProject.enumTableTypes.Stations, clsProject.enumTableTypes.PCodes, clsProject.enumTableTypes.Branches, clsProject.enumTableTypes.StaGrp, clsProject.enumTableTypes.PCodeGrp, clsProject.enumTableTypes.ConvGrp, clsProject.enumTableTypes.Criteria, clsProject.enumTableTypes.Tracking)

                    MainForm.WriteProgressOverall(i, 8, "Filling " & Project.TableName(tblType) & " table...")

                    Dim dg As DataGridView = Choose(i, dgStations, dgPCodes, dgBranches, dgStaGrp, dgPCodeGrp, dgConvGrp, dgCriteria, dgTracking)
                    With dg

                        'manually assign the data table columns to the datagrid, to avoid problems with inserting unbound columns (where is put in wrong order)
                        .AutoGenerateColumns = False
                        .Columns.Clear()

                        .SuspendLayout()
                        .DataSource = Nothing
                        .Font = New Font("Tahoma", 8, FontStyle.Regular, GraphicsUnit.Point)
                        .ReadOnly = False
                        .AllowUserToOrderColumns = False
                        .AllowUserToResizeRows = False
                        .AllowUserToResizeColumns = True
                        .AllowUserToDeleteRows = True
                        .AllowUserToAddRows = True
                        .RowHeadersVisible = False
                        .ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
                        .SelectionMode = DataGridViewSelectionMode.CellSelect
                        .StandardTab = False
                        Dim dt As DataTable = Project.DataSet.Tables(Project.TableName(tblType))
                        Dim bs As New BindingSource(Project.DataSet, Project.TableName(tblType))
                        bs.Sort = dt.Columns(0).ColumnName
                        .DataSource = bs

                        For c As Int16 = 0 To dt.Columns.Count - 1
                            'manually assign the data table columns to the datagrid, to avoid problems with inserting unbound columns (where is put in wrong order)
                            .Columns.Add(dt.Columns(c).ColumnName, dt.Columns(c).Caption)
                            .Columns(c).DataPropertyName = dt.Columns(c).ColumnName

                            With .Columns(c)
                                '.HeaderText = dt.Columns(c).Caption
                                .SortMode = DataGridViewColumnSortMode.NotSortable 'causes column headings to be non-centered!
                                .AutoSizeMode = DataGridViewAutoSizeColumnMode.NotSet
                                If dt.Columns(c).DataType Is GetType(String) Then
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                                    If dt.Columns(c).MaxLength > 0 AndAlso TypeOf dg.Columns(c) Is DataGridViewTextBoxColumn Then CType(dg.Columns(c), DataGridViewTextBoxColumn).MaxInputLength = dt.Columns(c).MaxLength
                                ElseIf dt.Columns(c).DataType Is GetType(Boolean) Then
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                                ElseIf dt.Columns(c).DataType Is GetType(Date) Then
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                                    .DefaultCellStyle.Format = MyDateTimeFormat()
                                Else
                                    .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                                    .DefaultCellStyle.NullValue = "<Null>"
                                End If
                                '.AutoSizeMode = (IIf(i < clsProject.enumTableTypes.PCodes, DataGridViewAutoSizeColumnsMode.DisplayedCells, DataGridViewAutoSizeColumnsMode.AllCells))
                                If tblType = clsProject.enumTableTypes.Criteria Then .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                            End With
                        Next
                        .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                        .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False
                        .AutoResizeRows(DataGridViewAutoSizeRowsMode.DisplayedCells)
                        .AutoResizeColumns((IIf(tblType = clsProject.enumTableTypes.Stations, DataGridViewAutoSizeColumnsMode.DisplayedCells, DataGridViewAutoSizeColumnsMode.AllCells)))
                        If .ColumnCount > 1 Then .Columns(0).Frozen = True
                        .ReadOnly = Not Project.IsLoggedIn
                        .ResumeLayout(True)
                    End With
                Next

                'add non-bound column so can lookup WQX Method info

                'todo: these unbound columns do not work correctly with fill, paste, etc.; they appear to not be updated but if you user cursor to move around, the new values appear!

                With dgStations
                    Dim col As New DataGridViewComboBoxColumn
                    col.Name = "Fips_Code_String"
                    col.HeaderText = "FIPS Code, County, and State"
                    col.Sorted = False
                    col.ValueType = GetType(String)
                    .Columns.Insert(18, col)

                    'hide FIPS bound column
                    .Columns("FIPS_Code").Visible = False
                    .Columns("County_Name").Visible = False
                    .Columns("State").Visible = False

                    .Columns("Convert_Station_ID").Visible = False 'this is deprecated
                    .Columns("Create_Update").ReadOnly = True
                    .Columns("Create_Update").DefaultCellStyle.ForeColor = Color.Gray
                End With

                'add non-bound column so can lookup WQX Method info
                With dgPCodes
                    Dim col As New DataGridViewComboBoxColumn
                    col.Name = "WQX_AnalyticalMethod_String"
                    col.HeaderText = "WQX Analytical Method"
                    col.ValueType = GetType(String)
                    .Columns.Insert(.Columns.Count - 2, col)

                    .Columns("WQX_AnalyticalMethod_String").Visible = Project.WQXVisible
                    .Columns("WQX_CharacteristicName").Visible = Project.WQXVisible
                    .Columns("WQX_ResultSampFraction").Visible = Project.WQXVisible
                    .Columns("WQX_ResultMeasureUnit").Visible = Project.WQXVisible
                    'hide bound WQX Method columns
                    .Columns(Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext")).Visible = False
                    .Columns(Project.DB.GetValidObjectName("WQX_AnalyticalMethodID")).Visible = False
                    .Columns(Project.DB.GetValidObjectName("WQX_AnalyticalMethodName")).Visible = False
                    .Columns(Project.DB.GetValidObjectName("WQX_AnalyticalMethodQualifType")).Visible = False
                    .Columns(Project.DB.GetValidObjectName("WQX_AnalyticalMethodDesc")).Visible = False

                    'hide column that no longer applies
                    .Columns("Sig_Figs").Visible = True
                    .Columns("Format_Str").Visible = False

                    .Columns("Convert_PCode").Visible = False 'these are deprecated
                    .Columns("Convert_Units").Visible = False
                    .Columns("Convert_Mult").Visible = False

                    .Columns("Create_Update").ReadOnly = True
                    .Columns("Create_Update").DefaultCellStyle.ForeColor = Color.Gray
                End With

                With dgTracking
                    .Columns(1).Frozen = True
                    .Columns("Project_Name").Visible = False
                End With

                'set default values for newly added rows

                With Project.DataSet
                    With .Tables("RCodes")
                        With .Columns("RCode")
                            '.AllowDBNull = False
                            .AllowDBNull = True
                            .DefaultValue = ""
                        End With
                        .Columns("Calc_Stats").DefaultValue = True
                        .Columns("Detect_Mult").DefaultValue = 1.0
                    End With

                    With .Tables("Tracking")
                        'If .Columns.Contains("Project_Name") Then .Columns("Project_Name").DefaultValue = Project.Name
                        If ActiveBrowser IsNot Nothing Then .Columns("Temp_DB_Name").DefaultValue = ActiveBrowser.TableName
                        .Columns("Master_DB_Name").DefaultValue = Project.MasterTableName
                        .Columns("Create_Date").DefaultValue = Now
                        .Columns("Create_Init").DefaultValue = MainWRDB.UserName
                        .Columns("Is_Import").DefaultValue = False
                    End With
                End With

                'combo initially says "Working Tables", so set filter

                bsTracking = New BindingSource(dgTracking.DataSource, dgTracking.DataMember)
                dgTracking.DataSource = bsTracking
                Me.dgTracking.Columns("Is_Import").Visible = False
                FilterString = ""
                bsTracking.Filter = GetFilter()

                FillTrackingCombos()

                RefreshValidateGrid()

                ''list unique table groups and put in combo box

                'With Me.cboTableGroup
                '    .Items.Clear()
                '    .Items.Add("All Working Tables")
                '    If Me.dgTracking.Columns.Contains("TableGroup") Then
                '        .Items.AddRange(Project.DB.GetRecordList("Tracking", "TableGroup", "WHERE Is_Import=" & Project.DB.Logical(False)).ToArray)
                '    End If
                '    .SelectedIndex = 0
                '    bsTracking.RemoveFilter()
                '    bsTracking.Filter = GetFilter()
                'End With
                'If Me.dgTracking.Columns.Contains("Project_Name") Then
                '    Me.dgTracking.Columns("Project_Name").Visible = Me.chkAllProjects.Checked
                'End If
                'dgTracking.AutoResizeRows(DataGridViewAutoSizeRowsMode.DisplayedCells)
                'dgTracking.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            If bwGetTable IsNot Nothing Then
                bwGetTable.Dispose()
                bwGetTable = Nothing
            End If
            MainForm.ProgressCompleteOverall()
            MainForm.WriteProgress("Formatting grids")
            ResetGrids()
            MainForm.ProgressComplete()
            EnableControls(True)
            UseWaitCursor = False
        End Try
    End Sub

    ''' <summary>
    ''' list unique table groups and put in combo box
    ''' </summary>
    Private Sub FillTrackingCombos()
        With Me.cboTableGroup
            .Items.Clear()
            .Items.Add("All Working Tables")
            If Me.dgTracking.Columns.Contains("TableGroup") Then
                .Items.AddRange(Project.DB.GetRecordList("Tracking", "TableGroup", "WHERE Is_Import=" & Project.DB.Logical(False)).ToArray)
            End If
            .SelectedIndex = 0
            bsTracking.RemoveFilter()
            bsTracking.Filter = GetFilter()
        End With
        'If Me.dgTracking.Columns.Contains("Project_Name") Then
        '    Me.dgTracking.Columns("Project_Name").Visible = Me.chkAllProjects.Checked
        'End If
        dgTracking.AutoResizeRows(DataGridViewAutoSizeRowsMode.DisplayedCells)
        dgTracking.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    End Sub

    Private UpdatePending As Boolean = False

    Private Sub ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs)
        If Project.DataSet.HasChanges Then UpdatePending = True
    End Sub

    Private Sub RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
        If UpdatePending Then
            UpdateTables()
            UpdatePending = False
        End If
    End Sub

    Private Sub cboDisplay_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboDisplay.SelectionChangeCommitted, chkAllProjects.CheckedChanged, cboTableGroup.SelectionChangeCommitted
        FilterTracking()
    End Sub

    Private Sub FilterTracking()
        Try
            Select Case Me.cboDisplay.SelectedIndex
                Case 0 'Working tables only
                    Me.dgTracking.Columns("Is_Import").Visible = False
                    Me.cboTableGroup.Enabled = True
                Case 1 'Import operations only
                    Me.dgTracking.Columns("Is_Import").Visible = False
                    'Me.cboTableGroup.SelectedIndex = 0
                    Me.cboTableGroup.Enabled = False
                Case 2 'both
                    Me.dgTracking.Columns("Is_Import").Visible = True
                    'Me.cboTableGroup.SelectedIndex = 0
                    Me.cboTableGroup.Enabled = False
            End Select
            'If Me.dgTracking.Columns.Contains("Project_Name") Then
            '    Me.dgTracking.Columns("Project_Name").Visible = Me.chkAllProjects.Checked
            'End If
            bsTracking.RemoveFilter()
            bsTracking.Filter = GetFilter()
            dgTracking.AutoResizeRows(DataGridViewAutoSizeRowsMode.DisplayedCells)
            dgTracking.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function GetFilter() As String
        If TabControl1.SelectedIndex = 6 Then
            Dim s As String = FilterString & IIf(FilterString = "", "", " AND ")
            Select Case Me.cboDisplay.SelectedIndex
                Case 0 'Working tables only
                    s &= "Is_Import=" & Project.DB.Logical(False)
                Case 1 'Import operations only
                    s &= "Is_Import=" & Project.DB.Logical(True)
                Case 2 'both
                    s &= ""
            End Select

            If Me.dgTracking.Columns.Contains("TableGroup") AndAlso Me.cboTableGroup.SelectedIndex <> 0 AndAlso cboTableGroup.Enabled Then
                If cboTableGroup.Text.Contains("'") Then
                    WarningMsg("Table groups must not contain the single quote character: '")
                    Return s
                End If
                s &= IIf(s = "", "", " AND ") & String.Format("(TableGroup='{0}' OR ISNULL(TableGroup,'')='') AND Is_Import={1}", cboTableGroup.Text, Project.DB.Logical(False))
            End If

            Return s
        Else
            Return FilterString
        End If
    End Function

    Private Sub btnFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFilter.Click
        Try
            With New frmFilter
                .DataGrid = ActiveGrid()
                .txtFilterString.Text = FilterString
                .ShowDialog(Me)
                FilterString = .txtFilterString.Text
                .Dispose()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Try
            With New frmFind
                .DataGrid = ActiveGrid()
                .Show(Me)
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub TabControl1_TabIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        ResetGrids()
        If TabControl1.SelectedIndex = 5 Then
            Static Alreadyshown As Boolean = False
            If Not Alreadyshown Then frmInfoMsg.ShowInfoMsg("Criteria Override Support Table", "A new Support table has been added to WRDB 5 which allows you to configure criteria overrides. Normally, water quality criteria limits are taken from the PCodes table, however you can use the Criteria Override table to override them based on Station ID (or one or several other Stations table fields).\n\nEnter a unique name, the PCode, an optional comma separated list of applicable month numbers, and a value for one (and only one) of the Stations fields (e.g., Station ID, Station Type, River Basin, etc.). Finally, enter minimum and maximum criteria limits (empty cells mean there is no limit).\n\nWhen the Criteria Limits report is generated, violations will be first based on limits found in the PCodes table and then upon the overrides in this table. Care must be taken when selecting different Stations fields, because a Station ID theoretically could match more than one entry in the override table. In this case overrides will be adopted based on the numeric value found in the Priority column (a larger number causes that row's rule to take precedence over other rules).")
            Alreadyshown = True
        End If
    End Sub

    Private Sub ResetGrids(Optional ByVal TabNum As Integer = -1)
        Try
            UpdateValidateTable(True) 'update pending edits in validate tables if change tabs
            Dim TableType As clsProject.enumTableTypes
            Dim FieldName As String
            FilterString = ""
            'when grids are filled, columns are not resized for non-visible tabs--do it here
            If TabNum = -1 Then TabNum = TabControl1.SelectedIndex
            If TabNum = 3 Then 'do all group grids
                For i As Integer = 1 To 3
                    With CType(Choose(i, dgStaGrp, dgPCodeGrp, dgConvGrp), DataGridView)
                        .AllowUserToResizeColumns = False
                        For c As Integer = 0 To Choose(i, 1, 1, 2)
                            With .Columns(c)
                                .Frozen = False
                                .FillWeight = 100 '* (c + 1)
                            End With
                        Next
                        .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                    End With
                    With CType(Choose(i, cboStationGrp, cboPCodeGrp, cboConvGrp), ComboBox)
                        .Items.Clear()
                        .Items.Add("(All Groups)")
                        Dim sql As String = String.Format("SELECT DISTINCT {0} FROM {1} ORDER BY {0}", _
                            Choose(i, "StationGroup", "PCodeGroup", "ConvGroup"), _
                            Choose(i, "StaGrp", "PCodeGrp", "ConvGrp"))
                        Dim dr As DbDataReader = Project.DB.ExecuteReader(sql)
                        While dr IsNot Nothing AndAlso dr.Read
                            If Not dr.IsDBNull(0) Then .Items.Add(dr.GetString(0))
                        End While
                        dr.Close()
                        .SelectedIndex = 0
                    End With
                Next
            ElseIf TabNum = 0 Then 'set drop-downs based on validation tables
                With dgeStations
                    For c As Integer = 0 To .DataGridView.ColumnCount - 1
                        FieldName = ""
                        Select Case .DataGridView.Columns(c).Name.ToUpper
                            Case "AGENCY"
                                TableType = clsProject.enumTableTypes.Agencies : FieldName = "Agency"
                            Case "BRANCH_ID"
                                TableType = clsProject.enumTableTypes.Branches : FieldName = "Branch_ID"
                            Case "STATION_TYPE", "BASIN_NAME", "WATERBODYTYPE", "ECOREGION", "PLANREGION"
                                TableType = clsProject.enumTableTypes.Validation : FieldName = "Valid_Value"
                            Case "FIPS_CODE_STRING"
                                TableType = clsProject.enumTableTypes.FIPSCodes : FieldName = "FIPS_Code"
                        End Select
                        If FieldName <> "" Then
                            If chkStationDropdown.Checked Then
                                Dim col As New Generic.List(Of String)
                                If FieldName = "FIPS_Code" Then
                                    .ReadOnlyColumn(c) = False
                                    Dim dr As DbDataReader = Project.DB.ExecuteReader("SELECT FIPS_Code, County_Name, State FROM FIPSCodes ORDER BY FIPS_Code")
                                    While dr IsNot Nothing AndAlso dr.Read
                                        col.Add(String.Format("{0} - {1}, {2}", TestNull(dr.GetValue(0), ""), TestNull(dr.GetValue(1), ""), TestNull(dr.GetValue(2), "")))
                                    End While
                                    dr.Close()
                                Else
                                    Dim WhereClause As String = ""
                                    If TableType = clsProject.enumTableTypes.Validation Then WhereClause = String.Format("WHERE {0}(Field_Name)='{1}'", Project.DB.UCase, .DataGridView.Columns(c).Name.ToUpper)
                                    col = Project.DB.GetRecordList(Project.TableName(TableType), FieldName, WhereClause)
                                End If
                                .SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, c, col)
                            Else
                                .SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.None, c)
                                If FieldName = "FIPS_Code" Then .ReadOnlyColumn(c) = True
                            End If
                        End If
                    Next
                    '.DataGridView.EditMode = DataGridViewEditMode.EditOnEnter
                End With
            ElseIf TabNum = 1 Then 'PCodes lookups
                With dgePCodes
                    For c As Integer = 0 To .DataGridView.ColumnCount - 1
                        FieldName = ""
                        Select Case .DataGridView.Columns(c).Name.ToUpper
                            Case "WQX_CHARACTERISTICNAME", "WQX_RESULTSAMPFRACTION", "WQX_RESULTMEASUREUNIT"
                                TableType = clsProject.enumTableTypes.Validation : FieldName = "Valid_Value"
                            Case "WQX_ANALYTICALMETHOD_STRING"
                                TableType = clsProject.enumTableTypes.WQXMethods : FieldName = Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext")
                        End Select
                        If FieldName <> "" Then
                            If Not .DataGridView.Columns(c).Name.ToUpper.StartsWith("WQX") OrElse Project.WQXVisible Then
                                If chkPCodeDropDown.Checked Then
                                    Dim col As New Generic.List(Of String)
                                    If .DataGridView.Columns(c).Name = Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext") Then
                                        .ReadOnlyColumn(c) = False
                                        Dim sep As String = String.Format("{0} ' ~ ' {0}", Project.DB.Concat)
                                        Dim dr As DbDataReader = Project.DB.ExecuteReader("SELECT {1} {0} WQX_AnalyticalMethodID {0} WQX_AnalyticalMethodName FROM WQXMethods", sep, Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext"))
                                        While dr IsNot Nothing AndAlso dr.Read
                                            col.Add(dr.GetString(0))
                                        End While
                                        dr.Close()
                                    Else
                                        Dim WhereClause As String = ""
                                        If TableType = clsProject.enumTableTypes.Validation Then WhereClause = String.Format("WHERE {0}(Field_Name)='{1}'", Project.DB.UCase, .DataGridView.Columns(c).Name.ToUpper)
                                        col = Project.DB.GetRecordList(Project.TableName(TableType), FieldName, WhereClause)
                                    End If
                                    .SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, c, col)
                                Else
                                    .SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.None, c)
                                    If FieldName = Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext") Then .ReadOnlyColumn(c) = True
                                End If
                            End If
                        End If
                    Next
                End With
            ElseIf TabNum = 5 Then 'set drop-downs based on validation tables
                With dgeCriteria
                    For c As Integer = 0 To .DataGridView.ColumnCount - 1
                        FieldName = ""
                        Select Case .DataGridView.Columns(c).Name.ToUpper
                            Case "PCODE"
                                TableType = clsProject.enumTableTypes.PCodes : FieldName = "PCode"
                            Case "STATION_ID", "HUC", "REACH_ID", "REMARKS", "OTHER", "COUNTY_NAME"
                                TableType = clsProject.enumTableTypes.Stations : FieldName = .DataGridView.Columns(c).Name
                            Case "AGENCY"
                                TableType = clsProject.enumTableTypes.Agencies : FieldName = "Agency"
                            Case "BRANCH_ID"
                                TableType = clsProject.enumTableTypes.Branches : FieldName = "Branch_ID"
                            Case "STATION_TYPE", "BASIN_NAME", "WATERBODYTYPE", "ECOREGION", "PLANREGION"
                                TableType = clsProject.enumTableTypes.Validation : FieldName = "Valid_Value"
                        End Select
                        If FieldName <> "" Then
                            If chkCriteriaDropDown.Checked Then
                                Dim col As New Generic.List(Of String)
                                Dim WhereClause As String = ""
                                If TableType = clsProject.enumTableTypes.Validation Then WhereClause = String.Format("WHERE {0}(Field_Name)='{1}'", Project.DB.UCase, .DataGridView.Columns(c).Name.ToUpper)
                                col = Project.DB.GetRecordList(Project.TableName(TableType), FieldName, WhereClause)
                                .SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.ComboBox, c, col)
                            Else
                                .SetColumnFormat(WRDB.Controls.DGVEditor.enumColumnTypes.None, c)
                            End If
                        End If
                    Next
                End With
            ElseIf TabNum = 6 Then
                'refresh tracking table filter
                FilterTracking()
            End If
            If TabNum <> 3 Then
                With CType(Choose(TabNum + 1, dgStations, dgPCodes, dgBranches, dgStaGrp, dgValidate, dgCriteria, dgTracking), DataGridView)
                    .AutoResizeColumns(IIf(TabControl1.SelectedIndex <= 1, DataGridViewAutoSizeColumnsMode.DisplayedCells, DataGridViewAutoSizeColumnsMode.AllCells))
                End With
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function ActiveGrid() As DataGridView
        Return Choose(Me.TabControl1.SelectedIndex + 1, Me.dgStations, Me.dgPCodes, Me.dgBranches, Me.dgStaGrp, Me.dgValidate, Me.dgCriteria, Me.dgTracking)
    End Function

    Private Sub btnBuild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuild.Click
        Try
            With New frmSupportGroup
                .ShowDialog()
                LoadTables()
                .Dispose()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnSync_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSync.Click
        Try
            If MessageBox.Show(StringFormat("This routine will examine the the Tracking table and sychronize the entries with currently available Working tables; the following updates will be automatically made:\n\n\t\b Tracking records with no Working tables will be deleted\n\t\b Working tables having no Tracking records will be added\n\t\b WRDB 4.5 entries containing '.DB' will be corrected\n\nYou will not be able to undo these changes. Are you sure you want to proceed?"), "Synchronize Tracking Table with Working Tables", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
                MainForm.WriteProgress("Synchronizing tables", ProgressBarStyle.Marquee)
                UpdateTables() 'save any changes first
                Try
                    Project.SynchTrackingRecords(False)
                Catch ex As Exception
                    WarningMsg(ex.Message)
                End Try
                LoadTables(clsProject.enumTableTypes.Tracking)
                MainForm.ProgressComplete()
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub dgPCodes_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgPCodes.CellFormatting
        With dgPCodes
            If .Columns(e.ColumnIndex).Name = "WQX_AnalyticalMethod_String" Then 'form appropriate string based on hidden columns
                Dim s As String = String.Format("{0} ~ {1} ~ {2}", .Item(Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext"), e.RowIndex).Value, .Item("WQX_AnalyticalMethodID", e.RowIndex).Value, .Item("WQX_AnalyticalMethodName", e.RowIndex).Value)
                If s = " ~  ~ " Then e.Value = DBNull.Value Else e.Value = s
            End If
        End With
    End Sub

    Private Sub dgStations_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgStations.CellFormatting
        With dgStations
            If .Columns(e.ColumnIndex).Name = "Fips_Code_String" Then 'form appropriate string based on hidden columns
                If e.RowIndex < .FirstDisplayedScrollingRowIndex OrElse
                    e.RowIndex > .FirstDisplayedScrollingRowIndex + .DisplayedRowCount(True) OrElse
                    e.ColumnIndex < .FirstDisplayedScrollingColumnIndex OrElse
                    e.ColumnIndex > .FirstDisplayedScrollingColumnIndex + .DisplayedColumnCount(True) Then Exit Sub
                Dim FIPS_Code As Integer = Val(TestNull(.Item("FIPS_Code", e.RowIndex).Value, -1))
                Dim County_Name As String = TestNull(.Item("County_Name", e.RowIndex).Value, "")
                Dim State As String = TestNull(.Item("State", e.RowIndex).Value, "")
                Dim WhereClause As String = "WHERE "
                If FIPS_Code <> -1 Then
                    WhereClause &= "FIPS_Code = " & FIPS_Code
                ElseIf County_Name <> "" And State <> "" Then
                    WhereClause &= String.Format("County_Name='{0}' AND {2}='{1}'", County_Name, State, Project.DB.GetValidObjectName("State"))
                ElseIf County_Name <> "" Then
                    WhereClause &= String.Format("County_Name='{0}'", County_Name)
                Else
                    e.Value = DBNull.Value
                    Exit Sub
                End If
                If FIPS_Code <> -1 Then
                    If County_Name = "" Or State = "" Then
                        Dim dr As DbDataReader = Project.DB.ExecuteReader("SELECT Fips_Code,County_Name,{0} FROM FIPSCodes " & WhereClause, Project.DB.GetValidObjectName("State"))
                        If dr IsNot Nothing And dr.Read Then
                            FIPS_Code = TestNull(dr.GetValue(0), -1)
                            County_Name = TestNull(dr.GetValue(1), "")
                            State = TestNull(dr.GetValue(2), "")
                        End If
                        dr.Close()
                    End If
                    e.Value = String.Format("{0} - {1}, {2}", FIPS_Code, County_Name, State)
                Else
                    e.Value = DBNull.Value
                End If
            End If
        End With
    End Sub

    'Private Sub dg_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgPCodes.CellFormatting, dgStations.CellFormatting, dgBranches.CellFormatting, dgCCodes.CellFormatting, dgQCodes.CellFormatting, dgRCodes.CellFormatting, dgStaGrp.CellFormatting, dgTracking.CellFormatting, dgAgencies.CellFormatting
    '    If Project.DB.DatabaseType = clsDB.enumDBType.SQL_Server Then 'for some reason, does not obey datagridview nullvalue setting
    '        With CType(sender, DataGridView)
    '            If .Item(e.ColumnIndex, e.RowIndex).Value.isnull Then e.Value = "" : e.FormattingApplied = True
    '        End With
    '    End If
    'End Sub

    ''' <summary>
    ''' Force key fields to be upper case
    ''' </summary>
    Private Sub dg_CellValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgPCodes.CellValidated, dgStations.CellValidated, dgBranches.CellValidated, dgStaGrp.CellValidated, dgPCodeGrp.CellValidated, dgConvGrp.CellValidated, dgTracking.CellValidated, dgCriteria.CellValidated
        Dim KeyFields As New Generic.List(Of String)
        'KeyFields.AddRange(New String() {"STATION_ID", "PCODE", "CCODE", "RCODE", "CONVERT_STATION_ID", "CONVERT_PCODE", "CONVERT_CCODE", "CONVERT_RCODE", "QCODE", "BRANCH_ID", "DS_BRANCH_ID", "STATIONGROUP", "PCODEGROUP", "CONVGROUP", "AGENCY", "STATION_TYPE", "WATERBODYTYPE", "BASIN_NAME", "ECOREGION", "PLANREGION", "STATE"})
        KeyFields.AddRange(New String() {"STATION_ID", "PCODE", "CCODE", "RCODE", "SCODE", "LCODE", "CONVERT_STATION_ID", "CONVERT_PCODE", "CONVERT_CCODE", "CONVERT_RCODE", "QCODE", "BRANCH_ID", "DS_BRANCH_ID", "STATIONGROUP", "PCODEGROUP", "CONVGROUP", "AGENCY", "STATION_TYPE", "STATE"})
        With CType(sender, DataGridView)
            If .Item(e.ColumnIndex, e.RowIndex).Value IsNot Nothing AndAlso Not IsDBNull(.Item(e.ColumnIndex, e.RowIndex).Value) Then
                Dim FieldName As String = CType(sender, DataGridView).Columns(e.ColumnIndex).Name
                If KeyFields.Contains(FieldName.ToUpper) Then
                    Dim Value As String = .Item(e.ColumnIndex, e.RowIndex).Value.ToString
                    If Value <> Value.ToUpper Then .Item(e.ColumnIndex, e.RowIndex).Value = Value.ToUpper
                End If
            End If
        End With
    End Sub

    Private Sub dg_CellValidatedPCode(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgPCodes.CellValidated
        With dgPCodes
            If .Columns(e.ColumnIndex).Name = "WQX_AnalyticalMethod_String" Then
                Dim s As String = .Item(e.ColumnIndex, e.RowIndex).Value
                If String.IsNullOrEmpty(s) Then Exit Sub
                Dim ar() As String = StringSplit(s, " ~ ")
                For i As Integer = 1 To 3
                    .Item(Choose(i, Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext"), "WQX_AnalyticalMethodID", "WQX_AnalyticalMethodName"), e.RowIndex).Value = IIf(ar(i - 1).Trim = "", DBNull.Value, ar(i - 1).Trim)
                Next
            End If
        End With
    End Sub

    Private Sub dgValidate_CellValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgValidate.CellEndEdit 'dgValidate.CellValidated  
        Dim KeyFields As New Generic.List(Of String)
        KeyFields.AddRange(New String() {"SCODE", "LCODE", "AGENCY", "STATION_TYPE"})
        With dgValidate
            If e.RowIndex = .RowCount - 1 Then Exit Sub 'ignore last row where insertions are done
            Select Case cboValidate.SelectedIndex
                Case 0 To 1, 4 To 6
                    If e.ColumnIndex = 0 Then
                        .Item(e.ColumnIndex, e.RowIndex).Value = TestNull(.Item(e.ColumnIndex, e.RowIndex).Value, "").ToUpper
                    End If
                Case 2 To 3, 7 To 14
                    .Item(0, e.RowIndex).Value = .Columns(1).HeaderText.ToUpper
                    Dim Value As String = CStr(TestNull(.Item(1, e.RowIndex).Value, ""))
                    If e.ColumnIndex = 1 Then
                        .Item(1, e.RowIndex).Value = IIf(KeyFields.Contains(.Columns(1).HeaderText.ToUpper), Value.ToUpper, Value)
                    ElseIf e.ColumnIndex = 2 Then
                        If IsDBNull(.Item(1, e.RowIndex).Value) Then .Item(1, e.RowIndex).Value = "New"
                    End If
            End Select
        End With
    End Sub

    Private Sub dg_CellValidatedStations(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgStations.CellValidated
        With dgStations
            If .Columns(e.ColumnIndex).Name = "Fips_Code_String" Then
                If TestNull(.Item(e.ColumnIndex, e.RowIndex).Value, "") = "" Then Exit Sub
                Dim FipsCode As String = StringSplit(.Item(e.ColumnIndex, e.RowIndex).Value, " - ")(0)
                If Not IsNumeric(FipsCode) Then Exit Sub
                Dim dr As DbDataReader = Project.DB.ExecuteReader("SELECT County_Name, State from FIPSCodes WHERE FIPS_Code={0}", Val(FipsCode))
                Dim County_Name As String = "", State As String = ""
                If dr IsNot Nothing AndAlso dr.Read Then
                    County_Name = dr.GetString(0)
                    State = dr.GetString(1)
                End If
                dr.Close()
                For i As Integer = 1 To 3
                    .Item(Project.DB.GetValidObjectName(Choose(i, "FIPS_Code", "County_Name", "State")), e.RowIndex).Value = Choose(i, FipsCode, County_Name, State)
                Next
            End If
        End With
    End Sub

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "3")
    End Sub

    Private Sub btnGIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGIS.Click
        Try
            MainForm.CheckGisData()
            With New WRDB.GIS.frmGIS(Project, ActiveTableName, clsProject.TempData, GIS.frmGIS.enumMode.DisplayStations, MainWRDB.WRDBStationTypes, Project.GisBasemapStations)
                .Show(Me)
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub dgGrp_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgStaGrp.GotFocus, dgPCodeGrp.GotFocus, dgConvGrp.GotFocus
        With CType(sender, DataGridView)
            lblNumRecGroup.Text = String.Format("{0:N0} records", CType(sender, DataGridView).Rows.Count)
        End With
    End Sub

    Private Sub dg_RowsAdded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsAddedEventArgs) Handles dgBranches.RowsAdded, dgStations.RowsAdded, dgPCodes.RowsAdded, dgStaGrp.RowsAdded, dgTracking.RowsAdded, dgValidate.RowsAdded, dgCriteria.RowsAdded
        Dim lbl As Label = Nothing
        If sender Is dgStations Then
            lbl = lblNumRecSta
        ElseIf sender Is dgPCodes Then
            lbl = lblNumRecPCode
        ElseIf sender Is dgConvGrp Then
            lbl = lblNumRecGroup
        ElseIf sender Is dgStaGrp Then
            lbl = lblNumRecGroup
        ElseIf sender Is dgPCodeGrp Then
            lbl = lblNumRecGroup
        ElseIf sender Is dgValidate Then
            lbl = lblNumRecValid
            'If Not dgValidate.Columns(0).Visible Then 'must force this field value to be same as column heading of column 1 (e.g., SCode)
            '    dgValidate.Item(0, e.RowIndex).Value = dgValidate.Columns(1).HeaderText
            'End If
        ElseIf sender Is dgCriteria Then
            lbl = lblNumRecCriteria
        ElseIf sender Is dgBranches Then
            lbl = lblNumRecBranch
        ElseIf sender Is dgTracking Then
            lbl = lblNumRecTracking
        End If
        lbl.Text = String.Format("{0:N0} records", CType(sender, DataGridView).Rows.Count - 1)
    End Sub

    Private Sub dg_RowsRemoved(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles dgBranches.RowsRemoved, dgStations.RowsRemoved, dgPCodes.RowsRemoved, dgStaGrp.RowsRemoved, dgTracking.RowsRemoved, dgValidate.RowsRemoved, dgCriteria.RowsRemoved
        Dim lbl As Label = Nothing
        If sender Is dgStations Then
            lbl = lblNumRecSta
        ElseIf sender Is dgPCodes Then
            lbl = lblNumRecPCode
        ElseIf sender Is dgConvGrp Then
            lbl = lblNumRecGroup
        ElseIf sender Is dgStaGrp Then
            lbl = lblNumRecGroup
        ElseIf sender Is dgPCodeGrp Then
            lbl = lblNumRecGroup
        ElseIf sender Is dgValidate Then
            lbl = lblNumRecValid
        ElseIf sender Is dgCriteria Then
            lbl = lblNumRecCriteria
        ElseIf sender Is dgBranches Then
            lbl = lblNumRecBranch
        ElseIf sender Is dgTracking Then
            lbl = lblNumRecTracking
        End If
        lbl.Text = String.Format("{0:N0} records", CType(sender, DataGridView).Rows.Count - 1)
    End Sub

    Private Sub cboGrp_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboStationGrp.SelectedIndexChanged, cboPCodeGrp.SelectedIndexChanged, cboConvGrp.SelectedIndexChanged
        If sender Is cboStationGrp Then
            ResetFilter(dgStaGrp, "StationGroup", cboStationGrp.Text)
        ElseIf sender Is cboPCodeGrp Then
            ResetFilter(dgPCodeGrp, "PCodeGroup", cboPCodeGrp.Text)
        ElseIf sender Is cboConvGrp Then
            ResetFilter(dgConvGrp, "ConvGroup", cboConvGrp.Text)
        End If
    End Sub

    Private Sub ResetFilter(ByVal DataGrid As DataGridView, ByVal ColName As String, ByVal GroupName As String)
        Dim bs As New BindingSource(DataGrid.DataSource, DataGrid.DataMember)
        Try
            Dim FilterString As String = ""
            If Not GroupName.StartsWith("(") Then
                FilterString = String.Format("{0} = '{1}'", ColName, GroupName.Replace("'", ""))
            End If
            If FilterString = "" Then
                bs.RemoveFilter()
            Else
                bs.RemoveFilter()
                bs.Filter = FilterString
            End If
            DataGrid.DataSource = bs
        Catch ex As Exception
            WarningMsg("An invalid filter expression was encountered:\n\n" & ex.Message)
            bs.RemoveFilter()
        End Try
    End Sub

    Private Sub btnCheck_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCheck.Click
        If WarningMsg("This will check all Support tables and make sure that there are no missing support records. For example, it is possible to change Stations records such that their Station Types do not exist in the StationTypes validation table. All such omissions will be reported and you will have the opportunity to append missing records.\n\nDo you want to continue?") = Windows.Forms.DialogResult.OK Then
            Dim NumChanges As Integer = 0
            For i As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Tracking
                If Project.DataSet.Tables(Project.TableName(i)).GetChanges IsNot Nothing Then NumChanges += Project.DataSet.Tables(Project.TableName(i)).GetChanges.Rows.Count
            Next
            If NumChanges > 0 AndAlso WarningMsgFmt("You have made changes to {0} rows in one or more of these Support tables. Do you want to write these changes to the database?", NumChanges) = Windows.Forms.DialogResult.OK Then
                If Not UpdateTables() Then Exit Sub
            End If
            CheckRecords()
        End If
    End Sub

    Private Function CheckRecords() As Boolean
        Try
            Dim SomeFound As Boolean = False
            For i As Integer = 1 To 3
                MainForm.WriteProgress(i, 3, "Checking tables...", ProgressBarStyle.Blocks)
                Dim TableName As String = Choose(i, "Stations", "PCodes", "Tracking")
                Dim NumFields As Integer = Choose(i, 8, 8, 1)
                For j As Integer = 1 To NumFields
                    'note: some WQX field names are too long for Paradox, so must truncate
                    Dim FieldName As String = Choose(i, Choose(j, "Agency", "Station_Type", "Branch_ID", "Basin_Name", "FIPS_Code", "WaterBodyType", "EcoRegion", "PlanRegion"),
                                                        Choose(j, "WQX_CharacteristicName", "WQX_ResultSampFraction", "WQX_ResultMeasureUnit", Project.DB.GetValidObjectName("WQX_AnalyticalMethodIDContext"), "WQX_AnalyticalMethodID", "WQX_AnalyticalMethodName", Project.DB.GetValidObjectName("WQX_AnalyticalMethodQualifType"), Project.DB.GetValidObjectName("WQX_AnalyticalMethodDesc")),
                                                        Choose(j, "QCode"))
                    Dim ValName As String = Choose(i, Choose(j, "Agencies", "Validation", "Branches", "Validation", "FIPSCodes", "Validation", "Validation", "Validation"),
                                                      Choose(j, "Validation", "Validation", "Validation", "WQXMethods", "WQXMethods", "WQXMethods", "WQXMethods", "WQXMethods"),
                                                      Choose(j, "QCodes"))
                    'get list of unique field values in support and validation tables
                    Dim lstSpt As Generic.List(Of String) = Project.DB.GetRecordList(TableName, FieldName)
                    Dim lstVal As Generic.List(Of String)
                    If ValName = "Validation" Then
                        lstVal = Project.DB.GetRecordList(ValName, "Valid_Value", String.Format("WHERE Field_Name='{0}'", FieldName))
                    Else
                        lstVal = Project.DB.GetRecordList(ValName, FieldName)
                    End If
                    'see what fields are missing from Validation table
                    Dim lstMissing As New Generic.List(Of String)
                    For Each s As String In lstSpt
                        If s.Trim <> "" AndAlso Not lstVal.Contains(s) Then lstMissing.Add(s)
                    Next
                    If lstMissing.Count > 0 Then
                        SomeFound = True
                        Dim sMissing As String = ""
                        If lstMissing.Count > 50 Then
                            Dim s As String = String.Format("({0} more not shown)", lstMissing.Count - 50)
                            lstMissing.RemoveRange(50, lstMissing.Count - 50)
                            lstMissing.Add(s)
                        End If
                        If WarningMsgFmt("The following items were found in the {0} field of the {1} table, but not in the {2} table:\n\n{3}\n\nDo you want to add these missing records?", FieldName, TableName, ValName, ArrayToCSV(lstMissing.ToArray).Replace(",", ", ")) = Windows.Forms.DialogResult.OK Then
                            For Each s As String In lstMissing
                                Dim sqlString As String
                                If ValName = "Validation" Then
                                    sqlString = String.Format("INSERT INTO {0} (Field_Name,Valid_Value) VALUES ('{1}','{2}')", ValName, FieldName, s)
                                ElseIf ValName = "WQXMethods" Then
                                    sqlString = String.Format("INSERT INTO {0} (WQX_AnalyticalMethodIDContext,{1}) VALUES ('UNKNOWN','{2}')", ValName, FieldName, s)
                                Else
                                    sqlString = String.Format("INSERT INTO {0} ({1}) VALUES ('{2}')", ValName, FieldName, s)
                                End If
                                Dim NumRec As Integer = Project.DB.ExecuteNonQuery(sqlString)
                                If NumRec = -2 Then
                                    WarningMsg("Unable to add new record to table.", sqlString)
                                    Return False
                                End If
                            Next
                        End If
                    End If
                Next
            Next
            If Not SomeFound Then WarningMsg("Validation table records were found for all fields.")
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            MainForm.ProgressComplete()
        End Try
    End Function

    Private Sub cboValidate_SelectedIndexCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboValidate.SelectionChangeCommitted
        RefreshValidateGrid()
    End Sub

    Private Sub RefreshValidateGrid()
        Try
            'data set table Validate will get reused for all table names and types

            Application.DoEvents()
            MainForm.WriteProgress("Refreshing grid...", ProgressBarStyle.Marquee)

            If Not UpdateValidateTable(True) Then Exit Sub

            Dim tablename As String = ""
            Select Case cboValidate.SelectedIndex
                Case 0 : tablename = Project.TableName(clsProject.enumTableTypes.CCodes)
                Case 1 : tablename = Project.TableName(clsProject.enumTableTypes.RCodes)
                Case 2 To 3 : tablename = Project.TableName(clsProject.enumTableTypes.Validation)
                Case 4 : tablename = Project.TableName(clsProject.enumTableTypes.QCodes)
                Case 5 : tablename = Project.TableName(clsProject.enumTableTypes.Agencies)
                Case 6 : tablename = Project.TableName(clsProject.enumTableTypes.FIPSCodes)
                Case 7 To 14 : tablename = Project.TableName(clsProject.enumTableTypes.Validation)
                Case 15 : tablename = Project.TableName(clsProject.enumTableTypes.WQXMethods)
            End Select

            If Project.DataSet.Tables.Contains("Validate") Then Project.DataSet.Tables.Remove("Validate")
            ValidateDataAdapter = Project.DB.AddTable(Project.DataSet, "Validate", "SELECT * FROM {0}", tablename)
            Dim dt As DataTable = Project.DataSet.Tables(tablename)
            With dgValidate
                .EndEdit()
                .SuspendLayout()
                .DataSource = Nothing
                .Font = New Font("Tahoma", 8, FontStyle.Regular, GraphicsUnit.Point)
                .ReadOnly = False
                .AllowUserToOrderColumns = False
                .AllowUserToResizeRows = False
                .AllowUserToResizeColumns = True
                .AllowUserToDeleteRows = True
                .AllowUserToAddRows = True
                .RowHeadersVisible = False
                .ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .StandardTab = False
                Dim bs As New BindingSource(Project.DataSet, "Validate")
                bs.Sort = dt.Columns(0).ColumnName
                .DataSource = bs
                For c As Int16 = 0 To .Columns.Count - 1
                    With .Columns(c)
                        .HeaderText = dt.Columns(c).Caption
                        .SortMode = DataGridViewColumnSortMode.NotSortable 'causes column headings to be non-centered!
                        .AutoSizeMode = DataGridViewAutoSizeColumnMode.NotSet
                        If dt.Columns(c).DataType Is GetType(String) Then
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                            If dt.Columns(c).MaxLength > 0 Then CType(dgValidate.Columns(c), DataGridViewTextBoxColumn).MaxInputLength = dt.Columns(c).MaxLength
                        ElseIf dt.Columns(c).DataType Is GetType(Boolean) Then
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                        ElseIf dt.Columns(c).DataType Is GetType(Date) Then
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                            .DefaultCellStyle.Format = MyDateTimeFormat()
                        Else
                            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                            .DefaultCellStyle.NullValue = "<Null>"
                        End If
                    End With
                Next
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False
                .AutoResizeRows(DataGridViewAutoSizeRowsMode.DisplayedCells)
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                If Project.GetTableType(tablename) = clsProject.enumTableTypes.Validation Then
                    Dim FieldName As String = Choose(cboValidate.SelectedIndex + 1, "CCode", "RCode", "SCode", "LCode", "QCode", "Agency", "FIPS_Code", "Station_Type", "Basin_Name", "WaterBodyType", "EcoRegion", "PlanRegion", "WQX_CharacteristicName", "WQX_ResultSampFraction", "WQX_ResultMeasureUnit", "WQX_Methods")
                    .Columns(0).Visible = False
                    .Columns(1).HeaderText = FieldName
                    bs.Filter = String.Format("Field_Name='{0}'", FieldName)
                    bs.Sort = "Field_Name, Valid_Value"
                    .Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                Else
                    If .ColumnCount > 1 Then .Columns(0).Frozen = True
                    For c As Integer = 0 To .ColumnCount - 1
                        .Columns(c).HeaderText = Project.GetTableTemplate(tablename).Columns(c).Caption
                        .Columns(c).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                    Next
                    If Project.GetTableType(tablename) = clsProject.enumTableTypes.CCodes Then .Columns(.ColumnCount - 1).Visible = False 'hide convert to column which is deprecated
                End If
                .ResumeLayout(True)
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            MainForm.ProgressComplete()
        End Try
    End Sub

    ''' <summary>
    ''' If the Validate table in the dataset (which can hold one of several tables displayed in Validate grid) has changed, try to save it back to the table
    ''' </summary>
    ''' <returns>True if successful</returns>
    Private Function UpdateValidateTable(ByVal ForceUpdate As Boolean) As Boolean
        dgValidate.EndEdit()
        Dim dt As DataTable = Project.DataSet.Tables("Validate")
        If dt IsNot Nothing AndAlso ValidateDataAdapter IsNot Nothing Then
            If dt.GetChanges IsNot Nothing AndAlso (ForceUpdate OrElse WarningMsgFmt("Do you want to write these changes to the {0} validation table?", dgValidate.Columns(1).HeaderText) = Windows.Forms.DialogResult.OK) Then
                Try
                    ValidateDataAdapter.Update(dt)
                Catch ex As Exception
                    WarningMsgFmt(String.Format("An error occurred when trying to update the Validation Support table for {0}. Make sure that you don't have any duplicate IDs. The error message was:\n\n{1}\n\nPlease correct the problem and try again.", dgValidate.Columns(1).HeaderText, ex.Message))
                    dt.RejectChanges()
                    'Return False
                    Return True
                End Try
                dt.AcceptChanges()
            End If
        End If
        Return True
    End Function

    Private Function CheckCriteriaTable() As Boolean
        Dim dt As DataTable = Project.DataSet.Tables("Criteria")
        If dt.GetChanges Is Nothing Then Return True
        For Each r As DataRow In dt.GetChanges.Rows
            If IsDBNull(r.Item("CritGroup")) Or IsDBNull(r.Item("PCode")) Then
                WarningMsgFmt("Error! You must enter a Name and PCode for all rows in the Criteria table.")
                Return False
            End If
            If Not IsDBNull(r("Month_List")) AndAlso r("Month_List") <> "" Then
                For Each s As String In CStr(r("Month_List")).Split(",")
                    Dim v As Integer = Val(s)
                    If s.Contains(".") OrElse (v < 1 Or v > 12) Then
                        WarningMsgFmt("Error on row having Name = {0} and PCode = {1}; the Month List column must contain a comma-separated list of month numbers (1-12) or be empty (meaning the criteria applies to all months).", r("CritGroup"), r("PCode"))
                        Return False
                    End If
                Next
            End If
            Dim num As Integer = 0
            For c As Integer = dt.Columns.IndexOf("Station_ID") To dt.Columns.IndexOf("PlanRegion")
                If Not IsDBNull(r.Item(c)) AndAlso Trim(r.Item(c)) = "" Then r.Item(c) = DBNull.Value
                If Not IsDBNull(r.Item(c)) Then num += 1
            Next
            If num <> 1 Then
                WarningMsgFmt("Error on row having Name = {0} and PCode = {1}; you must enter a value in ONE of the Stations columns (e.g., Station ID to Water Plan Region).", r("CritGroup"), r("PCode"))
                Return False
            End If
            num = 0
            For c As Integer = dt.Columns.IndexOf("Min_Criteria") To dt.Columns.IndexOf("Max_Criteria")
                If Not IsDBNull(r.Item(c)) Then num += 1
            Next
            If num < 1 Then
                WarningMsgFmt("Error on row having Name = {0} and PCode = {1}; you must enter a value in ONE or BOTH of the Criteria columns.", r("CritGroup"), r("PCode"))
                Return False
            End If
        Next
        Return True
    End Function

    Private Sub chkDropdown_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkStationDropdown.Click, chkPCodeDropDown.Click, chkCriteriaDropDown.Click
        Enabled = False
        ResetGrids()
        Enabled = True
    End Sub

    Private Sub dge_ProgressChanged(ByVal sender As Object, ByVal e As ProgressEventArgs) Handles dgeBranches.ProgressChanged, dgeConvGrp.ProgressChanged, dgePCodeGrp.ProgressChanged, dgePCodes.ProgressChanged, dgeStaGrp.ProgressChanged, dgeStations.ProgressChanged, dgeTracking.ProgressChanged, dgeValidation.ProgressChanged, dgeCriteria.ProgressChanged
        e.Cancel = Not MainForm.WriteProgress(e.Percent, 100, "Updating table", ProgressBarStyle.Blocks, e.CanCancel)
    End Sub

    Private Sub dge_ProgressComplete(ByVal sender As Object) Handles dgeBranches.ProgressComplete, dgeConvGrp.ProgressComplete, dgePCodeGrp.ProgressComplete, dgePCodes.ProgressComplete, dgeStaGrp.ProgressComplete, dgeStations.ProgressComplete, dgeTracking.ProgressComplete, dgeValidation.ProgressComplete, dgeCriteria.ProgressComplete
        MainForm.ProgressComplete()
    End Sub

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
        btnApply.PerformClick()
        MainForm.FileMgmtSupportImport()
        LoadTables()
    End Sub

    Private Sub btnGroups_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGroups.Click
        Try
            With New frmSupportGroup
                .InitialGroupType = 2 'select table group option
                .ShowDialog()
                LoadTables()
                .Dispose()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

End Class
